﻿Imports System.Data.SqlClient

Public Class frmClientContacts

    Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
        Me.Close()
    End Sub


    Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        Me.ErrorProv.Clear()
      If Len(Me.txtContactName.Text.Trim) = 0 Then
            ErrorProv.SetError(Me.txtContactName, "Please enter contact's name")
            Me.txtContactName.Focus()
            Exit Sub

        ElseIf Len(Me.txtContactPhone.Text.Trim) = 0 Then
            ErrorProv.SetError(Me.txtContactPhone, "Please enter contact's address")
            Me.txtContactPhone.Focus()
            Exit Sub

        ElseIf Len(Me.txtContactPosition.Text.Trim) = 0 Then
            ErrorProv.SetError(Me.txtContactPosition, "Please enter contact's position")
            Me.txtContactPosition.Focus()
            Exit Sub

        ElseIf Me.txtContactEmail.Text.Trim.Length > 0 And _
          Not System.Text.RegularExpressions.Regex.IsMatch(Me.txtContactEmail.Text.Trim, "^[a-zA-Z][\w\.-]*[a-zA-Z0-9]@[a-zA-Z0-9][\w\.-]*[a-zA-Z0-9]\.[a-zA-Z][a-zA-Z\.]*[a-zA-Z]$") Then
            MsgBox("Please enter a valid email address or leave blank")
            Me.txtContactEmail.Focus()
            Exit Sub
        Else
            Try
                Me.Cursor = Cursors.WaitCursor

                Dim cmd As New SqlCommand
                Dim trans As SqlTransaction

                cnn.Open()
                trans = cnn.BeginTransaction
                cmd.Connection = cnn
                cmd.Transaction = trans

                cmd.Parameters.Clear()

                '1) built insert statement
                cmd.CommandText = "Insert Into ClientContacts (ClientID,Name,Position,Phone,Email,SavedUser) Values " & _
                                  "(@ClientID,@Name,@Position,@Phone,@Email,@SavedUser) "

                'Add values
                cmd.Parameters.Clear()
                cmd.Parameters.AddWithValue("@ClientID", Me.txtClientNo.Text.Trim)
                cmd.Parameters.AddWithValue("@Name", Me.txtContactName.Text.Trim)
                cmd.Parameters.AddWithValue("@Position", Me.txtContactPosition.Text.Trim)
                cmd.Parameters.AddWithValue("@Phone", Me.txtContactPhone.Text.Trim)
                cmd.Parameters.AddWithValue("@Email", Me.txtContactEmail.Text.Trim)
                cmd.Parameters.AddWithValue("@SavedUser", CurrentUser)

                cmd.ExecuteNonQuery()
                trans.Commit()
                cnn.Close()

                MsgBox("Saved Successfully!", MsgBoxStyle.Information, Me.Text)

                FillContactsList()

                'Clear
                Me.txtContactName.Clear()
                Me.txtContactPosition.Clear()
                Me.txtContactPhone.Clear()
                Me.txtContactName.Focus()
                Me.txtContactEmail.Clear()
                Me.txtContactName.Focus()

                Me.Cursor = Cursors.Default
            Catch ex As Exception
                Me.Cursor = Cursors.Default
                If cnn.State = ConnectionState.Open Then
                    cnn.Close()
                End If
                MsgBox(ex.ToString)
            End Try
        End If
    End Sub

    Public Sub FillContactsList()
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim cmd As New SqlCommand("Select * From ClientContacts Where ClientID=" & CInt(Me.txtClientNo.Text.Trim), cnn)
            Dim Reader As SqlDataReader

            Me.GridContactDetails.Rows.Clear()

            cnn.Open()
            Reader = cmd.ExecuteReader
            While Reader.Read
                Me.GridContactDetails.Rows.Add(New String() {Reader.Item("SNo"), Reader.Item("Name"), Reader.Item("Position"), _
                                                             Reader.Item("Email"), Reader.Item("Phone"), "Update", "Delete"})
            End While
            cnn.Close()

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Private Sub GridContactDetails_CellClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles GridContactDetails.CellClick
        If e.ColumnIndex = 5 Then
            Try
                Me.Cursor = Cursors.WaitCursor

                Dim cmd As New SqlCommand
                Dim Trans As SqlTransaction

                cnn.Open()
                Trans = cnn.BeginTransaction
                cmd.Connection = cnn
                cmd.Transaction = Trans

                'Save Contacts
                For Each row As DataGridViewRow In Me.GridContactDetails.Rows
                    cmd.Parameters.Clear()
                    cmd.CommandText = "Update ClientContacts Set Name=@Name,Position=@Position,Email=@Email,Phone=@Phone,SavedUser=@SavedUser " & _
                                      "Where SNo=" & row.Cells(0).Value

                    cmd.Parameters.AddWithValue("@Name", row.Cells(1).Value)
                    cmd.Parameters.AddWithValue("@Position", row.Cells(2).Value)
                    cmd.Parameters.AddWithValue("@Email", row.Cells(3).Value)
                    cmd.Parameters.AddWithValue("@Phone", row.Cells(4).Value)
                    cmd.Parameters.AddWithValue("@SavedUser", CurrentUser)

                    cmd.ExecuteNonQuery()
                Next

                Trans.Commit()
                cnn.Close()

                MsgBox("Updated Successfully!", MsgBoxStyle.Information, Me.Text)

                Me.Cursor = Cursors.Default
            Catch ex As Exception
                Me.Cursor = Cursors.Default
                If cnn.State = ConnectionState.Open Then
                    cnn.Close()
                End If
                MsgBox(ex.ToString)
            End Try

        ElseIf e.ColumnIndex = 6 Then
            If MsgBox("Confirm delete?", MsgBoxStyle.YesNo) = MsgBoxResult.Yes Then
                Try
                    Me.Cursor = Cursors.WaitCursor

                    Dim cmd As New SqlCommand("Delete From ClientContacts Where SNo=" & _
                                              Me.GridContactDetails.CurrentRow.Cells(0).Value, cnn)

                    cnn.Open()
                    cmd.ExecuteNonQuery()
                    cnn.Close()

                    FillContactsList()

                    Me.Cursor = Cursors.Default
                Catch ex As Exception
                    Me.Cursor = Cursors.Default
                    If cnn.State = ConnectionState.Open Then
                        cnn.Close()
                    End If
                    MsgBox(ex.ToString)
                End Try
            End If
        End If
    End Sub
End Class